package com.fsbm.ctech.maxis.ebpsbackend;

import java.io.*;
import java.text.*;
import java.util.*;
import java.util.Date;
import java.sql.*;

import com.fsbm.ctech.maxis.ebpsbackend.ConfigObject;

/**
 *
 * @author Lendy Tayag
 */

public class DBLog {
    static ConfigObject cfgObj = new ConfigObject();

    public static void createDeliveryRecord(String csvFileName, int csvItemCount, String type) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            connection = DriverManager.getConnection(cfgObj.databaseSourceName, cfgObj.databaseUserID, cfgObj.databasePassword);
            if (connection != null){
                java.sql.Timestamp csvDateTime = new java.sql.Timestamp(new Date().getTime());

                pstmt = connection.prepareStatement("INSERT INTO EmailDeliveryLog (csvFileName, csvItemCount, csvDateTime, type) VALUES (?,?,?,?)");
                pstmt.setString(1,csvFileName);
                pstmt.setInt(2,csvItemCount);
                pstmt.setTimestamp(3,csvDateTime);
                pstmt.setString(4,type);
                pstmt.execute();
                if (pstmt != null) {
                    pstmt.close();
                }
                connection.close();
            }
        } catch (Exception ex) {
            cfgObj.write("Process Name(createDeliveryRecord)- " + ex.getClass() + " " + ex.getMessage(), "DBERR");
            cfgObj.write("--- csvFileName = " + csvFileName + ", csvItemCount = " + csvItemCount + ", type = " + type, "DBERR");
        }
    }

    public static void updateDeliveryRecord(String csvFileName, String zipFileName) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            connection = DriverManager.getConnection(cfgObj.databaseSourceName, cfgObj.databaseUserID, cfgObj.databasePassword);
            if (connection != null){

                pstmt = connection.prepareStatement("UPDATE EmailDeliveryLog SET zipFileName = ? WHERE csvFileName = ?");
                pstmt.setString(1,zipFileName);
                pstmt.setString(2,csvFileName);
                pstmt.execute();
                if (pstmt != null) {
                    pstmt.close();
                }
                connection.close();
            }
        } catch (Exception ex) {
            cfgObj.write("Process Name(updateDeliveryRecord)- " + ex.getClass() + " " + ex.getMessage(), "DBERR");
            cfgObj.write("--- csvFileName = " + csvFileName + ", zipFileName = " + zipFileName, "DBERR");
        }
    }

    public static void updateDeliveryRecord(String ftpJobNo, String zipFileName, String ftpResponse) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
      Class.forName(cfgObj.databaseDriver);

            connection = DriverManager.getConnection(cfgObj.databaseSourceName, cfgObj.databaseUserID, cfgObj.databasePassword);
            if (connection != null){
                java.sql.Timestamp ftpDateTime = new java.sql.Timestamp(new Date().getTime());
                pstmt = connection.prepareStatement("UPDATE EmailDeliveryLog SET ftpJobNo = ?, ftpDateTime = ?, ftpResponse = ? WHERE zipFileName = ?");
                pstmt.setString(1,ftpJobNo);
                pstmt.setTimestamp(2,ftpDateTime);
                pstmt.setString(3,ftpResponse);
                pstmt.setString(4,zipFileName);
                pstmt.execute();
                if (pstmt != null) {
                    pstmt.close();
                }
                connection.close();
            }
        } catch (Exception ex) {
            cfgObj.write("Process Name(updateDeliveryRecord)- " + ex.getClass() + " " + ex.getMessage(), "DBERR");
            cfgObj.write("--- ftpJobNo = " + ftpJobNo + ", zipFileName = " + zipFileName + ", ftpResponse = " + ftpResponse, "DBERR");
        }
    }

    public static void createReprintRecord(String csvFileName, int csvItemCount) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            connection = DriverManager.getConnection(cfgObj.databaseSourceName, cfgObj.databaseUserID, cfgObj.databasePassword);
            if (connection != null){
                java.sql.Timestamp csvDateTime = new java.sql.Timestamp(new Date().getTime());

                pstmt = connection.prepareStatement("INSERT INTO ReprintDeliveryLog (csvFileName, csvItemCount, csvDateTime) VALUES (?,?,?)");
                pstmt.setString(1,csvFileName);
                pstmt.setInt(2,csvItemCount);
                pstmt.setTimestamp(3,csvDateTime);
                pstmt.execute();
                if (pstmt != null) {
                    pstmt.close();
                }
                connection.close();
            }
        } catch (Exception ex) {
            cfgObj.write("Process Name(createReprintRecord)- " + ex.getClass() + " " + ex.getMessage(), "DBERR");
            cfgObj.write("--- csvFileName = " + csvFileName + ", csvItemCount = " + csvItemCount, "DBERR");
        }
    }

    public static void updateReprintRecord(String csvFileName, String zipFileName) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            connection = DriverManager.getConnection(cfgObj.databaseSourceName, cfgObj.databaseUserID, cfgObj.databasePassword);
            if (connection != null){

                pstmt = connection.prepareStatement("UPDATE ReprintDeliveryLog SET zipFileName = ? WHERE csvFileName = ?");
                pstmt.setString(1,zipFileName);
                pstmt.setString(2,csvFileName);
                pstmt.execute();
                if (pstmt != null) {
                    pstmt.close();
                }
                connection.close();
            }
        } catch (Exception ex) {
            cfgObj.write("Process Name(updateReprintRecord)- " + ex.getClass() + " " + ex.getMessage(), "DBERR");
            cfgObj.write("--- csvFileName = " + csvFileName + ", zipFileName = " + zipFileName, "DBERR");
        }
    }

    public static void updateReprintRecord(String zipFileName) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        System.out.println(zipFileName);
        try {
      Class.forName(cfgObj.databaseDriver);

            connection = DriverManager.getConnection(cfgObj.databaseSourceName, cfgObj.databaseUserID, cfgObj.databasePassword);
            if (connection != null){

                // Update ReprintDeliveryLog
                java.sql.Timestamp ftpDateTime = new java.sql.Timestamp(new Date().getTime());
                pstmt = connection.prepareStatement("UPDATE ReprintDeliveryLog SET ftpDateTime = ? WHERE zipFileName = ?");
                pstmt.setTimestamp(1,ftpDateTime);
                pstmt.setString(2,zipFileName);
                pstmt.execute();
                if (pstmt != null) {
                    pstmt.close();
                }

                // Update custlog
                pstmt = connection.prepareStatement("UPDATE custlog SET status = 'SUCC' WHERE activityId IN (SELECT activityId FROM ReprintDetailLog rdet, ReprintDeliveryLog rdel WHERE rdet.csvfilename = rdel.csvfilename and rdel.zipfilename = ?)");
                pstmt.setString(1,zipFileName);
                pstmt.execute();
                if (pstmt != null) {
                    pstmt.close();
                }

                connection.close();
            }
        } catch (Exception ex) {
            cfgObj.write("Process Name(updateReprintRecord)- " + ex.getClass() + " " + ex.getMessage(), "DBERR");
            cfgObj.write("--- zipFileName = " + zipFileName, "DBERR");
        }
    }

    public static void createReprintDetailRecord(String csvFileName, String activityId, int pages) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            connection = DriverManager.getConnection(cfgObj.databaseSourceName, cfgObj.databaseUserID, cfgObj.databasePassword);
            if (connection != null){

                pstmt = connection.prepareStatement("INSERT INTO ReprintDetailLog (csvFileName, activityId, pages) VALUES ('" + csvFileName + "'," + activityId + "," + pages + ")");

                pstmt.execute();
                if (pstmt != null) {
                    pstmt.close();
                }
                connection.close();
            }
        } catch (Exception ex) {
            cfgObj.write("Process Name(createReprintDetailRecord)- " + ex.getClass() + " " + ex.getMessage(), "DBERR");
            cfgObj.write("--- csvFileName = " + csvFileName + ", activityId = " + activityId + ", pages = " + pages, "DBERR");
        }
    }

} // end class DBLog


